﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Entity;

namespace DataAccess
{
    public class NewsDA
    {
        SqlDataAdapter da;
        public DataSet getNews()
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectNews", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool CreateNews(News n)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("addNews", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@Title", SqlDbType.NVarChar);
                    command.Parameters["@Title"].Value = n.New_Title.ToString();

                    command.Parameters.Add("@QuickTitle", SqlDbType.NVarChar);
                    command.Parameters["@QuickTitle"].Value = n.New_QuickTitle.ToString();

                    command.Parameters.Add("@Images", SqlDbType.NVarChar);
                    command.Parameters["@Images"].Value = n.New_Image.ToString();

                    command.Parameters.Add("@Contents", SqlDbType.NVarChar);
                    command.Parameters["@Contents"].Value = n.New_Content.ToString();

                    command.Parameters.Add("@NStatus", SqlDbType.Int);
                    command.Parameters["@NStatus"].Value = n.New_NStatus.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public News getNewByID(int id)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("selectNewsByID", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@id", id));
                    SqlDataReader dr = cmd.ExecuteReader();
                    News news = null;
                    if (dr.Read())
                    {
                        int nid = Int32.Parse(dr["NewsID"].ToString());
                        String newsTitle = dr["Title"].ToString();
                        String quickTitle = dr["QuickTitle"].ToString();
                        String image = dr["Images"].ToString();
                        String contents = dr["Contents"].ToString();
                        int status = Int32.Parse(dr["NStatus"].ToString());
                        news = new News(nid, newsTitle, quickTitle, image, contents, status);
                    }
                    connection.Close();
                    return news;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool UpdateNews(News n)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("updateNews", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@NewsID", SqlDbType.Int);
                    command.Parameters["@NewsID"].Value = n.New_ID.ToString();

                    command.Parameters.Add("@Title", SqlDbType.NVarChar);
                    command.Parameters["@Title"].Value = n.New_Title.ToString();

                    command.Parameters.Add("@QuickTitle", SqlDbType.NVarChar);
                    command.Parameters["@QuickTitle"].Value = n.New_QuickTitle.ToString();

                    command.Parameters.Add("@Images", SqlDbType.NVarChar);
                    command.Parameters["@Images"].Value = n.New_Image.ToString();

                    command.Parameters.Add("@Contents", SqlDbType.NVarChar);
                    command.Parameters["@Contents"].Value = n.New_Content.ToString();

                    command.Parameters.Add("@NStatus", SqlDbType.Int);
                    command.Parameters["@NStatus"].Value = n.New_NStatus.ToString();


                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public bool DeleteNews(News n)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("removeNews", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@NewsID", n.New_ID));
                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        //Lay tin tuc tien do du an
        //public DataSet getTT_TDDU(int status)
        //{
        //    using (SqlConnection connection = Database.GetConnection())
        //    {
        //        try
        //        {
        //            connection.Open();
        //            DataSet dt = null;
        //            da = new SqlDataAdapter("selectTinTucVSDuAn", connection);
        //            da.SelectCommand.CommandType = CommandType.StoredProcedure;
        //            da.SelectCommand.Parameters.Add(new SqlParameter("@NStatus", status));
        //            dt = new DataSet();
        //            da.Fill(dt);
        //            connection.Close();
        //            return dt;
        //        }
        //        catch (Exception e)
        //        {
        //            connection.Close();
        //            return null;
        //        }
        //    }
        //}
        //Lay 1 tin tuc theo Status
        public DataSet getTopNewsByStatus(int status)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectTopNewsByStatus", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@NStatus", status));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }
        public DataSet getTitleByStatus(int Status)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectNewsByStatus", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@NStatus", Status));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }
        public DataSet getNewByStatus(int currPage, int recodperpage, int Pagesize, int Status)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("spNews_PhanTrang", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@currPage", currPage));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@recodperpage", recodperpage));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@Pagesize", Pagesize));
                    da.SelectCommand.Parameters.Add(new SqlParameter("@Status", Status));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }
    }
}
